package com.itogis.rest.dao;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.stereotype.Component;

import com.itogis.rest.po.SysMenuSet;
import com.itogis.rest.po.SysRightInfo;
import com.itogis.rest.po.SysUserInfo;
import com.itogis.rest.po.VoteEvent;
import com.itogis.rest.po.VoteEventGroupTactics;
import com.itogis.rest.po.VoteLog;
import com.itogis.rest.po.VoteOption;
import com.itogis.rest.po.VotePageScript;
import com.itogis.rest.po.VoteResultStat;

/**
 * 
 * @author Administrator
 * 
 */
@Component
public class LoginDao extends BaseDao {
	/**
	 * 查询账号是否存在
	 * 
	 * @param loginId
	 * @return
	 * @throws SQLException
	 */
	public boolean isExistLoginId(String loginId) {
		String sql = " select  *  from  sys_userinfo where loginid='" + loginId
				+ "'";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public VoteEvent getVoteEventByName(String voteEventName) {
		String sql = " select * FROM VOTE_EVENT where name='" + voteEventName
				+ "'";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				VoteEvent voteEvent = new VoteEvent();
				voteEvent.setId(rs.getLong("ID"));
				voteEvent.setName(rs.getString("NAME"));
				voteEvent.setStatus(rs.getString("status"));
				voteEvent.setDescription(rs.getString("DESCRIPTION"));
				voteEvent.setStartTime(rs.getDate("START_TIME"));
				voteEvent.setEndTime(rs.getDate("END_TIME"));
				voteEvent.setType(rs.getString("TYPE"));
				voteEvent.setStyle(rs.getString("STYLE"));
				voteEvent.setPageUrl(rs.getString("PAGE_URL"));
				voteEvent.setVoteSourceType(rs.getString("VOTE_SOURCE_TYPE"));
				voteEvent.setVoteSourceId(rs.getString("VOTE_SOURCE_ID"));
				voteEvent.setPointType(rs.getString("POINT_TYPE"));
				voteEvent.setSpaceHour(rs.getLong("SPACE_HOUR"));
				voteEvent.setShowCheckMes(rs.getString("SHOW_CHECK_MES"));
				voteEvent.setMaxPoint(rs.getLong("MAX_POINT"));
				voteEvent.setMinPoint(rs.getLong("MIN_POINT"));
				voteEvent.setSpeakNone(rs.getString("SPEAK_NONE"));
				return voteEvent;
			} else {
				return null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public VoteEvent getVoteEventById(Long id) {
		String sql = " select * FROM VOTE_EVENT where id='" + id + "'";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				VoteEvent voteEvent = new VoteEvent();
				voteEvent.setId(rs.getLong("ID"));
				voteEvent.setName(rs.getString("NAME"));
				voteEvent.setStatus(rs.getString("status"));
				voteEvent.setDescription(rs.getString("DESCRIPTION"));
				voteEvent.setStartTime(rs.getDate("START_TIME"));
				voteEvent.setEndTime(rs.getDate("END_TIME"));
				voteEvent.setType(rs.getString("TYPE"));
				voteEvent.setStyle(rs.getString("STYLE"));
				voteEvent.setPageUrl(rs.getString("PAGE_URL"));
				voteEvent.setVoteSourceType(rs.getString("VOTE_SOURCE_TYPE"));
				voteEvent.setVoteSourceId(rs.getString("VOTE_SOURCE_ID"));
				voteEvent.setPointType(rs.getString("POINT_TYPE"));
				voteEvent.setSpaceHour(rs.getLong("SPACE_HOUR"));
				voteEvent.setShowCheckMes(rs.getString("SHOW_CHECK_MES"));
				voteEvent.setMaxPoint(rs.getLong("MAX_POINT"));
				voteEvent.setMinPoint(rs.getLong("MIN_POINT"));
				voteEvent.setSpeakNone(rs.getString("SPEAK_NONE"));
				voteEvent.setVoteGgroupId(rs.getString("VOTE_GROUP_ID"));
				return voteEvent;
			} else {
				return null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public VoteEventGroupTactics getVoteEventGroupTacticsById(Long id) {
		String sql = " select * FROM VOTE_EVENT_GROUP_TACTICS where id='" + id
				+ "'";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				VoteEventGroupTactics voteEvent = new VoteEventGroupTactics();
				voteEvent.setId(rs.getLong("ID"));
				voteEvent.setName(rs.getString("NAME"));
				voteEvent.setDescription(rs.getString("DESCRIPTION"));
				voteEvent.setRelGroupMax(rs.getLong("REL_GROUP_MAX"));
				voteEvent.setRelGroupMin(rs.getLong("REL_GROUP_MIN"));
				voteEvent.setVoteSumMax(rs.getLong("VOTE_SUM_MAX"));
				voteEvent.setVoteSumMin(rs.getLong("VOTE_SUM_MIN"));
				return voteEvent;
			} else {
				return null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}
	
	public VoteEventGroupTactics getVoteEventGroupTacticsByTacticsIdAndUserId(
			String tacticsId, String userId, String userIp, VoteEvent voteEvent) {
		String sql = "";
		sql = sql
				+ " select count(distinct t.event_id) event_amount,count(distinct t.option_id) option_amount,b.ID,b.NAME,b.DESCRIPTION,b.REL_GROUP_MIN,b.REL_GROUP_MAX,b.VOTE_SUM_MIN,b.VOTE_SUM_MAX from vote_log t ,vote_event v,vote_event_group_tactics b";
		sql = sql + " where t.event_id=v.id and v.vote_group_id=b.id and t.status='NORMAL'";
		if ("LOGIN".equals(voteEvent.getStyle())) {
			sql = sql + " and t.user_id='" + userId + "' and b.id='"
					+ tacticsId + "'";
		} else if ("LOGOUT".equals(voteEvent.getStyle())) {
			sql = sql + " and t.user_ip='" + userIp + "' and b.id='"
					+ tacticsId + "'";
		} else {
			sql = sql + " and (t.user_ip='" + userIp + "' or t.user_id='"
					+ userId + "') and b.id='" + tacticsId + "'";
		}
		sql = sql
				+ "and t.event_id!='"+voteEvent.getId()+"' group by   b.ID,b.NAME,b.DESCRIPTION,b.REL_GROUP_MIN,b.REL_GROUP_MAX,b.VOTE_SUM_MIN,b.VOTE_SUM_MAX";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				VoteEventGroupTactics voteEventGroupTactics = new VoteEventGroupTactics();
				voteEventGroupTactics.setId(rs.getLong("ID"));
				voteEventGroupTactics.setName(rs.getString("NAME"));
				voteEventGroupTactics.setDescription(rs
						.getString("DESCRIPTION"));
				voteEventGroupTactics.setRelGroupMax(rs
						.getLong("REL_GROUP_MAX"));
				voteEventGroupTactics.setRelGroupMin(rs
						.getLong("REL_GROUP_MIN"));
				voteEventGroupTactics.setVoteSumMax(rs.getLong("VOTE_SUM_MAX"));
				voteEventGroupTactics.setVoteSumMin(rs.getLong("VOTE_SUM_MIN"));
				voteEventGroupTactics.setOptionAmount(rs
						.getLong("option_amount"));
				voteEventGroupTactics
						.setEventAmount(rs.getLong("event_amount"));
				return voteEventGroupTactics;
			} else {
				return null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public VotePageScript getVoteScriptPageById(Long id) {
		String sql = " select SCRIPT,ID,NAME,MESSAGE,CREATEDATE,PAGE_URL,TYPE from VOTE_PAGE_SCRIPT where id='" + id
				+ "'";		
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				VotePageScript votePageScript=new VotePageScript();
				
				
				//默认的 ResultSet 对象不可更新，仅有一个向前移动的指针。因此，只能迭代它一次，并且只能按从第一行到最后一行的顺序进行。就是说ResultSet只能向前移动(rs.next())，而且只能按所取的记录的列的顺序来读取，所以这里：String str1 = rs.getString(field1);String str2 = rs.getString(field2);如果所取的记录里字段field2在field1之前，而读取的时候读field2在读field1之后，那么就会抛出上述异常！！解决方法非常简单：要么修改select语句使得field2在field1之前，要么修改读取语句的顺序！
				//long型字段必须是第一个
				Reader reader = rs.getCharacterStream("SCRIPT"); 
				BufferedReader bufReader = new BufferedReader(reader); 
				StringBuffer strBuf = new StringBuffer(); 
				String line; 
				try {
					while ((line = bufReader.readLine()) != null) { 
					strBuf.append(line); 
					strBuf.append("\r\n"); 
					}
					bufReader.close(); 
					//System.out.println("The content is:" + strBuf.toString()); 
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
				votePageScript.setScript(strBuf.toString());
				
				
				
				votePageScript.setId(rs.getLong("ID"));
				votePageScript.setName(rs.getString("NAME"));
				votePageScript.setMessage(rs.getString("MESSAGE"));
				votePageScript.setPageUrl(rs.getString("PAGE_URL"));				
				
				
				votePageScript.setType(rs.getString("TYPE"));
				return votePageScript;
			} else {
				return null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public String getWeekWorkByUseId(Long useId,String groupId) {
		String sql = " select MESSAGE from vote_option where VOTE_SOURCE_ID='" + useId+ "' and status='NORMAL' AND EVENT_ID IN (select t.id from vote_event t where t.vote_group_id='"+groupId+"')";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				return rs.getString("MESSAGE");
			} else {
				return null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public List<VoteOption> getVoteOptionByEventId(VoteEvent event,String userId,String userIp) {
		List<VoteOption> l = new ArrayList<VoteOption>();
		String sql = " ";
		
		sql = sql+" select K.*,NVL(G.check_TIMES,0)check_TIMES,G.speak_MES from VOTE_OPTION k,";
		sql = sql+" (";
		sql = sql+" select v.OPTION_ID,V.check_TIMES,b.speak_MES from ";
		sql = sql+" ( ";
		sql = sql+" select OPTION_ID,count(*) check_TIMES from vote_log where ";
		//是否用户登录可以投票 login  logout all
		if(event.getStyle().equals("LOGIN"))
		{	
			sql = sql+" user_id='"+userId+"' ";
		}
		else
		if(event.getStyle().equals("LOGOUT"))
		{	
			sql = sql+" USER_IP='"+userIp+"' ";
		}	
		else
		{
			sql = sql+" (USER_IP='"+userIp+"' or user_id='"+userId+"') ";
		}	
		sql = sql+" and status='NORMAL'";
		sql = sql+" and event_id='"+event.getId()+"' group BY OPTION_ID";
		sql = sql+" ) v ,";
		sql = sql+" (select OPTION_ID,strcat(MESSAGE) speak_MES from vote_cryptonym_mes_log where ";
		sql = sql+" USER_ID='"+userId+"' group by OPTION_ID) b";
		sql = sql+" where v.option_id=b.OPTION_ID(+)";

		sql = sql+" )g where k.id=g.option_id(+) and k.event_id='"+event.getId()+"' and k.status='NORMAL' order by k.SORT_INDEX";
		
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {

			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				VoteOption voteOption = new VoteOption();
				voteOption.setId(rs.getLong("ID"));
				voteOption.setMessage(rs.getString("MESSAGE"));
				voteOption.setSortIndex(rs.getLong("SORT_INDEX"));
				voteOption.setEventId(rs.getLong("EVENT_ID"));
				voteOption.setVoteSourceType(rs.getString("VOTE_SOURCE_TYPE"));
				voteOption.setVoteSourceId(rs.getString("VOTE_SOURCE_ID"));
				voteOption.setTitle(rs.getString("TITLE"));
				voteOption.setCanSpeak(rs.getString("CAN_SPEAK"));
				voteOption.setScores(rs.getLong("SCORES"));
				voteOption.setChecked(rs.getLong("check_TIMES"));
				voteOption.setCheckedSpeak(rs.getString("speak_MES"));
				l.add(voteOption);
			}
			return l;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public List<VoteResultStat> getVoteResultStatOnLineByEventIds(String eventIds) {
		List<VoteResultStat> l = new ArrayList<VoteResultStat>();
		String sql = "select qq.*,bb.speak_mes from(select a.id event_id,b.id option_id,b.title event_name,b.message option_mes,nvl(c.num,0) option_count, nvl(round(c.num /d.allnum*100, 0),0) RATE "
				+ " from vote_event a,vote_option b,(select t.option_id,COUNT(*) num from vote_log t where t.status = 'NORMAL'"
				+ "            and t.event_id in("+eventIds+") group by  t.option_id) c,"
				+ "(select t.EVENT_ID,COUNT(*) allnum from vote_log t where t.status = 'NORMAL' "
				+ "	and t.event_id  in("+eventIds+") group by t.EVENT_ID) d where a.id=b.event_id and b.id=c.option_id(+)	and a.id=d.EVENT_ID(+)	and a.id  in("+eventIds+") order by b.sort_index)" + " qq,(";
		sql = sql
				+ "  select t.option_id, strcat(decode(t.user_id,'-1','匿名',null,'匿名',a.username)||'：' || t.   message || '<br>') speak_mes";
		sql = sql + "  from vote_cryptonym_mes_log t,sys_userinfo a";
		sql = sql + "  where t.user_id=a.userid(+) and t.message is not null";
		sql = sql + "  and t.option_id in";
		sql = sql + "  (select id from vote_option where event_id  in("+eventIds+"))";
		sql = sql + "  group by t.option_id";
		sql = sql
				+ ") bb"
				+ " where qq.option_id=bb.option_id(+) order by qq.event_id,qq.option_count desc";

		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {

			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				VoteResultStat voteResultStat = new VoteResultStat();
				voteResultStat.setEvent_id(rs.getLong("EVENT_ID"));
				voteResultStat.setOption_id(rs.getLong("OPTION_ID"));
				voteResultStat.setEvent_name(rs.getString("EVENT_NAME"));
				voteResultStat.setOption_name(rs.getString("option_mes"));
				voteResultStat.setCount(rs.getLong("option_count"));
				voteResultStat.setRate(rs.getLong("RATE"));
				voteResultStat
						.setSpeak_mes(rs.getString("speak_mes") == null ? null
								: rs.getString("speak_mes").replaceAll(">,",
										">"));
				l.add(voteResultStat);
			}
			return l;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public List<VoteResultStat> getVoteResultStatByEventId(Long event_id) {
		List<VoteResultStat> l = new ArrayList<VoteResultStat>();
		String sql = " select * from VOTE_RESULT_STAT where event_id='"
				+ event_id + "'";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {

			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				VoteResultStat voteResultStat = new VoteResultStat();
				voteResultStat.setId(rs.getLong("ID"));
				voteResultStat.setEvent_id(rs.getLong("EVENT_ID"));
				voteResultStat.setOption_id(rs.getLong("OPTION_ID"));
				voteResultStat.setEvent_name(rs.getString("EVENT_NAME"));
				voteResultStat.setOption_name(rs.getString("OPTION_NAME"));
				voteResultStat.setCount(rs.getLong("COUNT"));
				voteResultStat.setRate(rs.getLong("RATE"));
				l.add(voteResultStat);
			}
			return l;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public List<VoteLog> getVoteLogByIpOrUserId(Long event_id, String ip,
			String user_id) {
		List<VoteLog> l = new ArrayList<VoteLog>();
		String sql = " select * from vote_log where event_id='" + event_id
				+ "' and status='NORMAL' AND (user_id='" + user_id
				+ "' or (user_ip='" + ip + "'))";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				VoteLog voteLog = new VoteLog();
				voteLog.setId(rs.getLong("ID"));
				voteLog.setEvent_id(event_id);
				voteLog.setEvent_name(rs.getString("EVENT_NAME"));
				voteLog.setMessage(rs.getString("message"));
				voteLog.setOption_id(rs.getLong("option_id"));
				voteLog.setOption_name(rs.getString("option_name"));
				voteLog.setStatus(rs.getString("status"));
				voteLog.setUser_id(rs.getLong("user_id"));
				voteLog.setUser_ip(rs.getString("user_ip"));

				l.add(voteLog);
			}
			return l;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public Date getMaxVoteLogDateByIpOrUserId(Long event_id, String ip,
			String user_id) {
		Date lastDate = null;
		String sql = " select max(CREATE_TIME)CREATE_TIME from vote_log where event_id='"
				+ event_id
				+ "' and status='NORMAL' AND (user_id='"
				+ user_id
				+ "' or (user_ip='" + ip + "'))";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				lastDate = rs.getDate("CREATE_TIME");
			}
			return lastDate;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public String insertVoteLog(VoteEvent voteEvent, VoteOption voteOption,
			String ip, String user_id) {
		String sql = "";
		String id = getSequences("REPCAT_LOG_SEQUENCE");
		sql = " insert into VOTE_LOG (ID,EVENT_ID,OPTION_ID,EVENT_NAME,OPTION_NAME,USER_IP,USER_ID,CREATE_TIME,STATUS) values "
				+ "("
				+ id
				+ ",'"
				+ voteEvent.getId()
				+ "','"
				+ voteOption.getId()
				+ "','"
				+ voteOption.getTitle()
				+ "','"
				+ voteOption.getMessage()
				+ "','"
				+ ip
				+ "',"
				+ user_id
				+ ",sysdate,'NORMAL')";

		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			int i = st.executeUpdate(sql);
			return (i == 1) ? id : "-1";
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return id;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public boolean insertVoteLogMessage(String voteOptionId, String message,
			String userId) {
		String sql2 = "";
		sql2 = "insert into VOTE_CRYPTONYM_MES_LOG(OPTION_ID,MESSAGE,USER_ID) values('"
				+ voteOptionId + "','" + message + "'," + userId + ")";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			int i = st.executeUpdate(sql2);
			return (i == 1) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public boolean iniVoteResultStat(VoteEvent voteEvent) {
		String sql = "insert into VOTE_RESULT_STAT  (ID, EVENT_ID, OPTION_ID, EVENT_NAME, OPTION_NAME, COUNT, RATE)"
				+ "select REPCAT_LOG_SEQUENCE.Nextval,qq.* from(select a.id event_id,b.id option_id,b.title event_name,b.message option_mes,nvl(c.num,0) option_count, nvl(round(c.num /d.allnum*100, 0),0) RATE "
				+ " from vote_event a,vote_option b,(select t.option_id,COUNT(*) num from vote_log t where t.status = 'NORMAL'"
				+ "            and t.event_id = '"
				+ voteEvent.getId()
				+ "' group by  t.option_id) c,"
				+ "(select t.EVENT_ID,COUNT(*) allnum from vote_log t where t.status = 'NORMAL' "
				+ "	and t.event_id = '"
				+ voteEvent.getId()
				+ "' group by t.EVENT_ID) d where a.id=b.event_id and b.id=c.option_id(+)	and a.id=d.EVENT_ID(+)	and a.id='"+voteEvent.getId()+"' order by b.sort_index)qq";
		String sql2 = "delete VOTE_RESULT_STAT where EVENT_ID='"
				+ voteEvent.getId() + "'";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			int i = st.executeUpdate(sql2);
			i = st.executeUpdate(sql);
			return (i == 1) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public boolean updateVoteLogEffact(VoteEvent voteEvent, String ip,
			String user_id) {
		String sql = " update vote_log set STATUS='EFFACE' where event_id='"
				+ voteEvent.getId() + "' and status='NORMAL' ";
		if (voteEvent.getStyle().equals("LOGIN")) {
			sql = sql + " AND user_id='" + user_id + "' ";
		} else if (voteEvent.getStyle().equals("LOGOUT")) {
			sql = sql + " AND user_ip='" + ip + "' ";
		} else {
			sql = sql + " AND (user_id='" + user_id + "' or user_ip='" + ip
					+ "') ";
		}
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			int i = st.executeUpdate(sql);
			return (i == 1) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public boolean isExistLoginIP(String IP) {
		String sql = " select  *  from  sys_userinfo where IP_ADDRESS='" + IP
				+ "'";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public String getSequences(String sequencesName) {
		String sql = " select  " + sequencesName
				+ ".Nextval sequencesName from  dual";
		String seq = "-9999";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				seq = rs.getString("sequencesName");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return seq;
		} finally {
			this.closeConnection(rs, st, con);
		}
		return seq;
	}

	/**
	 * 查询用户密码是否正确
	 * 
	 * @param loginId
	 * @param password
	 * @return
	 */
	public boolean checkPassword(String loginId, String password) {
		String sql = " select  *  from  sys_userinfo where loginid='" + loginId
				+ "' and password='" + password + "'";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	/**
	 * 查询用户密码是否正确
	 * 
	 * @param loginId
	 * @param password
	 * @return
	 */
	public boolean updatePassword(String userid, String password) {
		String sql = " update sys_userinfo set password='" + password
				+ "' where  userid='" + userid + "'";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			int i = st.executeUpdate(sql);
			return (i == 1) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public boolean updateWeekWork(String userid, String workMes,String groupId) {
		String sql = " update vote_option set message='" + workMes
				+ "' where  VOTE_SOURCE_ID='" + userid + "' and status='NORMAL' AND EVENT_ID IN (select t.id from vote_event t where t.vote_group_id='"+groupId+"')";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			int i = st.executeUpdate(sql);			
			return (i >= 1) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	public boolean updateLoginIP(String userid, String IP) {
		String sql = " update sys_userinfo set IP_ADDRESS='" + IP
				+ "' where  userid='" + userid + "'";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			int i = st.executeUpdate(sql);
			return (i == 1) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			this.closeConnection(rs, st, con);
		}
	}

	/**
	 * 根据用户账号获取用户权限菜单
	 * 
	 * @param userId
	 * @return
	 */
	public List getMenu(String userId) {
		List l = new ArrayList();
		String sql = " select  *  from sys_rightinfo where  rightid  in (select rightid from sys_roleright_r where roleid in ( select  roleid  from sys_roleuser_r where userid='"
				+ userId
				+ "')) and parentid=(select min(rightid) from sys_rightinfo) order by orderindex  ";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				SysRightInfo form = new SysRightInfo();
				form.setRightId(rs.getInt("rightid"));
				form.setRightName(rs.getString("rightname"));
				form.setRightType(rs.getString("righttype"));
				form.setRightUrl(rs.getString("righturl"));
				form.setPageId(rs.getString("pageid"));
				form.setReportId(rs.getString("reportid"));
				form.setPartId(rs.getString("partid"));
				form.setPartType(rs.getString("parttype"));
				form.setPermissionType(rs.getString("permissiontype"));
				l.add(form);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			this.closeConnection(rs, st, con);
		}
		return l;
	}
	
	/**
	 * 根据用户账号获取用户权限菜单
	 * 
	 * @param userId
	 * @return
	 */
	public List<SysRightInfo> getTreeMenu(String userId) {
		List<SysRightInfo> l = new ArrayList<SysRightInfo>();
		String sql ="select qq.*,nvl(bb.num,0)soncount from ( select distinct a.* from	sys_rightinfo a start with a.rightid in	(";
		sql=sql+"			 select t1.rightid from sys_rightinfo t1,sys_rightinfo t2";
		sql=sql+"					where t1.rightid=t2.parentid(+) and t2.parentid is null and t1.righttype in(0,1)";
		sql=sql+"					and t1.rightid in";
		sql=sql+"					(";
		sql=sql+"					 select  b.RIGHTID  from sys_roleuser_r a,sys_roleright_r b where USERID='"+userId+"'";
		sql=sql+"					and a.ROLEID=b.roleid";
		sql=sql+"					)";
		sql=sql+"	)";
		sql=sql+"	connect by prior a.parentid=a.rightid";
		
		sql=sql+" )qq ,(select k.parentid,count(k.rightid) num";
		sql=sql+"	from sys_rightinfo k";
		sql=sql+"	where k.parentid is not null";
		sql=sql+"	group by k.parentid) bb";
		sql=sql+"	where qq.rightid=bb.parentid(+) order by qq.parentid,qq.orderindex";
		
		System.out.println(sql);
		
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				SysRightInfo form = new SysRightInfo();
				form.setRightId(rs.getInt("rightid"));
				form.setRightName(rs.getString("rightname"));
				form.setRightType(rs.getString("righttype"));
				form.setRightUrl(rs.getString("righturl"));
				form.setPageId(rs.getString("pageid"));
				form.setReportId(rs.getString("reportid"));
				form.setPartId(rs.getString("partid"));
				form.setPartType(rs.getString("parttype"));
				form.setPermissionType(rs.getString("permissiontype"));
				form.setSonCount(rs.getInt("soncount"));
				form.setParentId(rs.getInt("PARENTID"));
				form.setDesc(rs.getString("REMARK"));
				form.setParams(rs.getString("params"));
				form.setOtherfilename(rs.getString("Otherfilename"));
				form.setConfigfilename(rs.getString("configfilename"));
				l.add(form);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			this.closeConnection(rs, st, con);
		}
		return l;
	}

	/**
	 * 根据用户账号获取用户权限菜单
	 * 
	 * @param userId
	 * @return
	 */
	public List getFunction(String userId) {
		List l = new ArrayList();
		String sql = " select  *  from sys_rightinfo where  rightid  in (select rightid from sys_roleright_r where roleid in ( select  roleid  from sys_roleuser_r where userid='"
				+ userId + "'))   order by orderindex  ";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				SysRightInfo form = new SysRightInfo();
				form.setRightId(rs.getInt("rightid"));
				form.setRightName(rs.getString("rightname"));
				form.setRightType(rs.getString("righttype"));
				form.setRightUrl(rs.getString("righturl"));
				form.setParentId(rs.getInt("parentid"));
				form.setImageUrl(rs.getString("imageurl"));
				l.add(form);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			this.closeConnection(rs, st, con);
		}
		return l;
	}

	/**
	 * 获取用户的ID
	 * 
	 * @param loginId
	 *            用户账号
	 * @return 用户ID
	 */
	public String getUserId(String loginId) {
		String sql = "select userid  from sys_userinfo where loginid='"
				+ loginId + "'";
		return this.findStringId(sql);
	}

	public String getUserName(String loginId) {
		String sql = "select USERNAME  from sys_userinfo where loginid='"
				+ loginId + "'";
		return this.findStringId(sql);
	}
	public String getUserKfbm(String loginId) {
		String sql = "select code  from sys_userinfo where loginid='"
				+ loginId + "'";
		return this.findStringId(sql);
	}
	
	public String getUserdepartment(String loginId) {
		String sql = "select department  from sys_userinfo where loginid='"
				+ loginId + "'";
		return this.findStringId(sql);
	}

	public SysUserInfo getUserIdByIP(String IP) {
		SysUserInfo sysUserInfo = new SysUserInfo();
		String sql = "select userid  from sys_userinfo where IP_ADDRESS='" + IP
				+ "'";
		sysUserInfo.setUserId(this.findStringId(sql));
		sql = "select loginid  from sys_userinfo where IP_ADDRESS='" + IP + "'";
		sysUserInfo.setLoginId(this.findStringId(sql));

		sql = "select password  from sys_userinfo where IP_ADDRESS='" + IP
				+ "'";
		sysUserInfo.setPassword(this.findStringId(sql));

		sql = "select USERNAME  from sys_userinfo where IP_ADDRESS='" + IP
				+ "'";
		sysUserInfo.setUserName(this.findStringId(sql));

		return sysUserInfo;
	}

	/**
	 * 获取菜单设置
	 * 
	 * @return SysMenuSet
	 */
	public SysMenuSet getMenuSet() {
		String sql = " select *  from sys_menuset t  where isdefault ='1' ";
		Connection con = this.getConnection();
		Statement st = null;
		ResultSet rs = null;
		SysMenuSet form = new SysMenuSet();
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				form.setId(rs.getInt("id"));
				form.setIndexPageName(rs.getString("indexpagename"));
				form.setIndexPage(rs.getString("indexpage"));
				form.setAutoexplaint(rs.getString("autoexplaint"));
				form.setHeight(rs.getInt("height"));
				form.setIsDefault(rs.getString("isdefault"));
				form.setMenuPlace(rs.getString("menuplace"));
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			this.closeConnection(rs, st, con);
		}
		return form;
	}
}
